<?php
/**
 * Smart Restaurant
 *
 * An open source application to manage restaurants
 *
 * @package		SmartRestaurant
 * @author		Gjergj Sheldija
 * @copyright	Copyright (c) 2008, Gjergj Sheldija
 * @license		http://www.gnu.org/licenses/gpl.txt
 * @since		Version 1.0
 * @filesource
 * 
 *  Smart Restaurant is free software: you can redistribute it and/or modify
 *	it under the terms of the GNU General Public License as published by
 *	the Free Software Foundation, version 3 of the License.
 *
 *	Smart Restaurant is distributed in the hope that it will be useful,
 *	but WITHOUT ANY WARRANTY; without even the implied warranty of
 *	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *	GNU General Public License for more details.

 *	You should have received a copy of the GNU General Public License
 *	along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */
/**
 * @author : mizuko
 * raporti per gjendjen e madhit ne magazine
 * vetem per artikujt e guzhines
 * ato qi printohen te printeri i guzhines
 */
require_once( 'Spreadsheet\Excel\Writer.php' );

$con = mysql_connect("localhost" , "root" , "") or die ("Not Fouund" . mysql_error());
mysql_select_db("resplus") or die ("Not Fouund" . mysql_error());
$select = "SELECT ".
"`stock_objects`.`name` AS 'Emri',".
"CASE WHEN `stock_objects`.`unit_type`='0' THEN 'Cope' WHEN `stock_objects`.`unit_type`='1' THEN 'KG' WHEN `stock_objects`.`unit_type`='2' THEN 'Liter' END  AS `Njesia`,".
"ROUND(`stock_objects`.`quantity`,2) AS `Sasia Aktuale`,".
"ROUND(`stock_objects`.`value`,2) AS `Cmimi i Blerjes`,".
"ROUND((`stock_objects`.`quantity`*`stock_objects`.`value`),2) AS `Vlera Cmimi i Blerjes`".
"FROM ".
"`stock_objects`".
"Inner Join `stock_ingredient_quantities` ON `stock_objects`.`id` = `stock_ingredient_quantities`.`obj_id`".
"Inner Join `dishes` ON `stock_ingredient_quantities`.`dish_id` = `dishes`.`id`".
"WHERE".
"`dishes`.`destid` = '1' AND `stock_objects`.`deleted` = '0'   AND `dishes`.`deleted` = '0'".
"GROUP BY".
"`stock_objects`.`name`,".
"`stock_objects`.`unit_type`".
"ORDER BY `stock_objects` .`name`";

$export = mysql_query($select); 
$fields = mysql_num_fields($export); 

// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

$emen = 'guzhina.xls';
// sending HTTP headers
$workbook->send($emen);

// Creating a worksheet
$worksheet =& $workbook->addWorksheet('Inventari Fizik - Guzhina');

//gjansia e kolonave
$worksheet->setColumn(0,0,3);
$worksheet->setColumn(0,1,20);
$worksheet->setColumn(1,2,8);
$worksheet->setColumn(2,3,8);
$worksheet->setColumn(4,4,8);
$worksheet->setColumn(6,5,14);


//krijoj formatin me perdore mandej
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_bold->setTextWrap();


//krijoj kokat e tabelave
$worksheet->write(0,0, 'Nr' , $format_bold ); 
$worksheet->write(0,1, 'Emri' , $format_bold ); 
$worksheet->write(0,2, 'Njesia'  , $format_bold ); 
$worksheet->write(0,3, 'Sasia Aktuale'  , $format_bold ); 
$worksheet->write(0,4, 'Cmimi Blerjes'  , $format_bold ); 
$worksheet->write(0,5, 'Vlera Cmimi i Blerjes', $format_bold ); 

$rreshti;

for ($i = 1 ; $row = mysql_fetch_assoc($export) ; $i++) {
    $worksheet->write($i,0, $i );     
	$worksheet->write($i,1, $row['Emri']); 
    $worksheet->write($i,2,  $row['Njesia'] ); 
    $worksheet->write($i,3,  $row['Sasia Aktuale'] );     
    $worksheet->write($i,4, $row['Cmimi i Blerjes'] );     
    $worksheet->write($i,5,  $row['Vlera Cmimi i Blerjes'] ) ;       
    $rreshti = $i;
} 

$rreshti+=1;

//llogaris shumen e kolonave
$worksheet->writeFormula($rreshti, 4, "=SUM(E1:E".$rreshti.")", $format_bold);
$worksheet->writeFormula($rreshti, 5, "=SUM(F1:F".$rreshti.")", $format_bold);


$workbook->close();
?>
